﻿
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ImportVersiuneAnterioara]
	-- Add the parameters for the stored procedure here
	@ServerName varchar(50), 
	@DBName varchar(50),
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @comanda varchar(5000)
    DECLARE @strCodUnitate varchar(10)

	BEGIN TRAN
	SET @strCodUnitate = cast(@CodUnitate as varchar(10))
    SET @Comanda = 'DELETE FROM [ConturiSpeciale] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [ConturiCoresp] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [RulajeDoc] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	


    SET @Comanda = 'DELETE FROM [Rulaje] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	
	print('Delete records from [DocumenteDetaliu] table')
	SET @Comanda = 'DELETE FROM [DocumenteDetaliu] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE			

	print('Delete records from [DocumenteDetaliu2010] table')
	SET @Comanda = 'DELETE FROM [DocumenteDetaliu2010] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE			

	
	print('Delete records from [Articole] table')
	SET @Comanda = 'DELETE FROM [Articole] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE		
	
    print('Delete records from [Conturi] table')
    SET @Comanda = 'DELETE FROM [Conturi] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Sterge Conturi - ok')

	SET @Comanda = 'INSERT INTO [Conturi]
           ([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[CodUnitate])
		SELECT [Cont],LTRIM(RTRIM([Denumire])),[Tip],[Terminal],[ContParinte],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Conturi]'    
    
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Import Conturi - ok')

    SET @Comanda = 'UPDATE [Conturi] SET [EsteAnalitic]=0 WHERE LEN(LTRIM(RTRIM([Cont])))<=4'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	print('Delete records from [Ordonantari] table')
    SET @Comanda = 'DELETE FROM [Ordonantari] WHERE [CodUnitate]=' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	print('Delete records from [ArticoleAliniate] table')
    SET @Comanda = 'DELETE FROM [ArticoleAliniate] WHERE [CodUnitate]=' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	


    SET @Comanda = 'INSERT INTO [ArticoleAliniate]
           ([ArtAl],[Denumire],[AAParinte],[Terminal],[Titlu],[Categorii],[TermDenumire],[StartDenumire],[Anexa6],[CodUnitate])
SELECT LTRIM(RTRIM([ArtAl])) [ArtAl] ,[Denumire],LTRIM(RTRIM([AAParinte])) [AAParinte],[Terminal] ,[Titlu],[Categorii],[TermDenumire],[StartDenumire],[Anexa6],'+@strCodUnitate+'           
FROM ['+@ServerName+'].['+@DBName+'].[dbo].[ConturiAA]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('ArticoleAliniarte - ok')
    SET @Comanda = 'DELETE FROM [Parteneri] WHERE [CodUnitate]=' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Parteneri] ON;INSERT INTO [Parteneri]
           ([IDPartener],[Denumire],[CUI],[IDBanca],[ContBancar],[Cont],[CodUnitate])
		SELECT		[IDFurnizor],[Denumire],CAST(REPLACE([CodFiscal],''.'','''') AS Decimal(13,0)),0,[ContBancar],[Cont],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Furnizori]  WHERE IDFurnizor<>0'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Parteneri - ok')

    SET @Comanda = 'INSERT INTO [ConturiSpeciale]
           ([Cont],[Tipuri],[Descriere],[CodUnitate])
			SELECT [Cont],[Tipuri],[Descriere],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[ConturiSpeciale]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('ConturiSpeciale - ok')
    SET @Comanda = 'DELETE FROM [Jurnale] WHERE [CodUnitate]=' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Parteneri] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Jurnale] ON;INSERT INTO [Jurnale]
           ([IdJurnal],[Denumire]
           ,[IDParinte]
           ,[Codificare]
           ,[Terminal],[CodUnitate])
SELECT [IdJurnal],[Denumire]
           ,[IDParinte]
           ,[Codificare]
           ,[Terminal],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Jurnale] WHERE [IDJurnal]<>[IdParinte]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Jurnale - ok')

    SET @Comanda = 'SET IDENTITY_INSERT [Jurnale] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Ordonantari] ON;INSERT INTO [Ordonantari]
           ([IDOrdonantare],[NrDoc]
           ,[DataDoc]
           ,[ArtAl]
           ,[IDFurnizor]
           ,[ValoareLei]
           ,[IDJurnal]
           ,[Scop]
           ,[DocJust]
           ,[AngLegal]
           ,[Achitat],[CodUnitate])
SELECT [IDOrdonantare],[NrDoc]
           ,[DataDoc]
           ,SUBSTRING(REPLACE([Cont],''7701.'',''''),1,40)
           ,[IDFurnizor]
           ,[ValoareLei]
           ,[IDJurnal]
           ,[Scop]
           ,[DocJust]
           ,[AngLegal]
           ,[Achitat],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Ordonantari]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Ordonantari - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [Ordonantari] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	


    SET @Comanda = 'SET IDENTITY_INSERT [RulajeDoc] ON;INSERT INTO [RulajeDoc]
           ([IDDocument],[ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate],[CodUnitate])
SELECT [IDDocument],[ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[RulajeDoc]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('RulajeDoc - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [RulajeDoc] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Rulaje] ON;INSERT INTO [Rulaje]
           ([CodFormula]
      ,[ContDebit]
      ,[ContCredit]
      ,[CodValuta]
      ,[IDDocument]
      ,[ValoareValuta]
      ,[Curs]
      ,[ValoareLei]
      ,[DataDocJust]
      ,[TipDocJust]
      ,[NumarDocJust]
      ,[ExplicatiiDocJust]
      ,[NrCrt],[CodUnitate])
SELECT [CodFormula]*1000
      ,[ContDebit]
      ,[ContCredit]
      ,[CodValuta]
      ,[IDDocument]
      ,[ValoareValuta]
      ,[Curs]
      ,[ValoareLei]
      ,[DataDocJust]
      ,[TipDocJust]
      ,[NumarDocJust]
      ,[ExplicatiiDocJust]
      ,[NrCrt],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Rulaje]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Rulaje - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [Rulaje] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [RulajeDoc] WHERE NOT EXISTS(SELECT * FROM [Rulaje] a 
 WHERE a.IdDocument=[RulajeDoc].IDDocument)'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

 	COMMIT



	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la preluarea datelor!',16,-1)
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:
	
END